﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data;
using System.IO;
namespace Ecreate.Utility
{
    public class GenerateCommSql
    {
        public static string GetPriamrySql(DataRow dr, DataTable dt)
        {
            string fields = string.Empty;
            string sql = string.Empty;
            DataColumn[] dcs = dt.PrimaryKey;

            DataTable table = dr.Table;
            foreach (DataColumn dc in dcs)
            {
                if (table.Columns[dc.ColumnName] == null) continue;
                if (dt.Columns.IndexOf(dc.ColumnName) != -1)
                {

                    if (dc.DataType.ToString() == "System.Int32")
                    {
                        fields = fields + " and " + dc.ColumnName + "={0}";
                        if (!dr.HasVersion(DataRowVersion.Original))
                        {
                            fields = string.Format(fields, dr[dc.ColumnName]);
                        }
                        else
                        {
                            fields = string.Format(fields, dr[dc.ColumnName, DataRowVersion.Original]);
                        }
                    }
                    else if (dc.DataType.ToString() == "System.Decimal")
                    {
                        fields = fields + " and " + dc.ColumnName + "={0}";
                        if (!dr.HasVersion(DataRowVersion.Original))
                        {
                            fields = string.Format(fields, dr[dc.ColumnName]);
                        }
                        else
                        {
                            fields = string.Format(fields, dr[dc.ColumnName, DataRowVersion.Original]);
                        }
                    }
                    else if (dc.DataType.ToString() == "System.Single")
                    {
                        fields = fields + " and " + dc.ColumnName + "={0}";
                        if (!dr.HasVersion(DataRowVersion.Original))
                        {
                            fields = string.Format(fields, dr[dc.ColumnName]);
                        }
                        else
                        {
                            fields = string.Format(fields, dr[dc.ColumnName, DataRowVersion.Original]);
                        }
                    }
                    else
                    {
                        fields = fields + " and " + dc.ColumnName + "='{0}'";
                        if (!dr.HasVersion(DataRowVersion.Original))
                        {
                            fields = string.Format(fields, dr[dc.ColumnName]);
                        }
                        else
                        {
                            fields = string.Format(fields, dr[dc.ColumnName, DataRowVersion.Original]);
                        }
                    }
                }
            }
            sql = " where 1=1 " + fields;
            return sql;
        }













        public static string GetPriamrySql(DataRow dr, DataTable dt, out List<System.Data.Common.DbParameter> list)
        {
            string fields = string.Empty;
            string sql = string.Empty;
            DataColumn[] dcs = dt.PrimaryKey;
            string OldName = "old_";
            string ParamName = string.Empty;
            list = new List<System.Data.Common.DbParameter>();
            foreach (DataColumn dc in dcs)
            {
                if (dt.Columns.IndexOf(dc.ColumnName) != -1)
                {

                    ParamName = "@" + OldName + dc.ColumnName;
                    fields = fields + " and " + dc.ColumnName + "=" + ParamName;
                    SqlDbType dbtype = getDbType(dc.DataType);
                    System.Data.Common.DbParameter param = new System.Data.SqlClient.SqlParameter(ParamName, dbtype, dc.MaxLength);
                    if (!dr.HasVersion(DataRowVersion.Original))
                    {
                        param.Value = dr[dc.ColumnName];
                    }
                    else
                    {
                        param.Value = dr[dc.ColumnName, DataRowVersion.Original];
                    }
                    list.Add(param);

                }
            }
            sql = " where 1=1 " + fields;
            return sql;
        }










        public static string GenerateUpdateSql(DataRow dr, DataTable dt)
        {
            string values = string.Empty;
            string sql = string.Empty;
            foreach (DataColumn dc in dt.Columns)
            {
                DataTable table = dr.Table;
                if (table.Columns[dc.ColumnName] == null) continue;

                object value = dr[dc.ColumnName];
                if (value == null) continue;
                if (value.ToString().Trim() == string.Empty) continue;
                if (dc.DataType.ToString() == "System.Int32")
                {
                    values = values + dc.ColumnName + "={0},";
                    values = string.Format(values, dr[dc.ColumnName]);
                }
                else if (dc.DataType.ToString() == "System.Decimal")
                {
                    values = values + dc.ColumnName + "={0},";
                    values = string.Format(values, dr[dc.ColumnName]);
                }
                else if (dc.DataType.ToString() == "System.Single")
                {
                    values = values + dc.ColumnName + "={0},";
                    values = string.Format(values, dr[dc.ColumnName]);
                }
                else
                {
                    values = values + dc.ColumnName + "='{0}',";
                    values = string.Format(values, dr[dc.ColumnName]);
                }

            }
            string where = GetPriamrySql(dr, dt);
            values = values.Substring(0, values.Length - 1);
            sql = " update " + dt.TableName + " set " + values + where;
            return sql;
        }





        public static string GenerateDeleteSql(DataRow dr, DataTable dt)
        {
            string sql = " delete from " + dt.TableName + GetPriamrySql(dr, dt);
            return sql;
        }


        public static SqlDbType getDbType(System.Type type)
        {
            if (type.ToString() == "System.Int32")
            {
                return SqlDbType.Int;
            }
            else if (type.ToString() == "System.Decimal")
            {
                return SqlDbType.Float;
            }
            else if (type.ToString() == "System.Single")
            {
                return SqlDbType.Float;
            }
            else if (type.ToString() == "System.String")
            {

                return SqlDbType.VarChar;
            }
            else if (type.ToString() == "System.DateTime")
            {
                return SqlDbType.DateTime;
            }
            else if (type.ToString() == "System.Byte[]")
            {
                return SqlDbType.VarBinary;

            }
            else if (type.ToString() == "")
            {
                return SqlDbType.Image;
            }
            return SqlDbType.DateTime;
        }


   

    


        public static void GenerateSql(DataTable dt, DataTable dtSchema)
        {
            string total = string.Empty;
            foreach (DataRow dr in dt.Rows)
            {
                string strsql = GenerateInsertSql(dr, dtSchema);
                total = total + strsql + "\n";
            }
            string FilePath = "c:\\" + dt.TableName + ".txt";
            FileInfo fi = new FileInfo(FilePath);
            FileStream fs = fi.Open(FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine(total);
            sw.Close();
            fs.Close();
        }


        public static string GenerateInsertSql(DataRow dr, DataTable dt)
        {
            string fields = string.Empty;
            string values = string.Empty;
            string sql = string.Empty;
            foreach (DataColumn dc in dt.Columns)
            {
                object value = dr[dc.ColumnName];
                //if (value == null) continue;
                //if (value.ToString() == string.Empty) continue;
                value = value.ToString().Replace("\\n", " ");
                value = value.ToString().Replace("\\r", " ");
                value = value.ToString().Replace("'", "''");

                if (dc.DataType.ToString() == "System.Int32")
                {
                    if (value.ToString() == string.Empty)
                    {
                        value = "0";
                    }

                    values = values + "{0}" + ",";
                    values = string.Format(values, value);
                }
                else if (dc.DataType.ToString() == "System.Decimal")
                {
                    if (value.ToString() == string.Empty)
                    {
                        value = "0";
                    }
                    values = values + "{0}" + ",";
                    values = string.Format(values, value);
                }
                else if (dc.DataType.ToString() == "System.Single")
                {
                    if (value.ToString() == string.Empty)
                    {
                        value = "0";
                    }
                    values = values + "{0}" + ",";
                    values = string.Format(values, value);
                }
                else if (dc.DataType == typeof(System.Boolean))
                {
                    if (Boolean.Parse(value.ToString()) == true)
                    {
                        value = "1";
                    }
                    else
                    {
                        value = "0";

                    }
                    values = values + "{0}" + ",";
                    values = string.Format(values, value);
                }

                else if (dc.DataType.ToString() == "System.Byte")
                {
                    if (Boolean.Parse(value.ToString()) == true)
                    {
                        value = "1";
                    }
                    else
                    {
                        value = "0";

                    }
                    values = values + "{0}" + ",";
                    values = string.Format(values, value);
                }
                else
                {
                    values = values + "'{0}'" + ",";
                    values = string.Format(values, value);
                }
                fields = fields + dc.ColumnName + ",";
            }

            fields = fields.Substring(0, fields.Length - 1);
            values = values.Substring(0, values.Length - 1);
            fields = "(" + fields + ")";
            values = "(" + values + ")";
            sql = " insert into " + dt.TableName + fields + "  values " + values;
            return sql;
        }
    }
}
